IF OBJECT_ID('ssf.dpnd_get_object_header') IS NOT NULL
	DROP FUNCTION ssf.dpnd_get_object_header
GO

CREATE FUNCTION ssf.dpnd_get_object_header (
	@dpnd_object_name NVARCHAR(256)
)
RETURNS TABLE 
AS
RETURN (
	SELECT r.object_type
		, r.dpnd_object_name
		, CAST((SELECT (SELECT dpnd_object_name, object_type, purpose, return_data_type, create_date, create_user
			FROM ssf.dpnd_header_object WITH (NOLOCK)
			WHERE header_object_id = r.header_object_id
			FOR XML RAW ('object_summary'), ELEMENTS, TYPE)
		, (SELECT parameter_order, parameter_name, parameter_desc, data_type, default_value, return_type
			FROM ssf.dpnd_header_parameter WITH (NOLOCK)
			WHERE header_object_id = r.header_object_id
			ORDER BY parameter_order
			FOR XML RAW ('parameter_dependency'), ELEMENTS, TYPE)
		, (SELECT object_type, dpnd_object_name
			FROM ssf.dpnd_dependency_object WITH (NOLOCK)
			WHERE header_object_id = r.header_object_id
			AND dependency_type = 'INPUT'
			FOR XML RAW ('input_dependency'), ELEMENTS, TYPE)
		, (SELECT object_type, dpnd_object_name
			FROM ssf.dpnd_dependency_object WITH (NOLOCK)
			WHERE header_object_id = r.header_object_id
			AND dependency_type = 'TEMPORARY'
			FOR XML RAW ('temporary_dependency'), ELEMENTS, TYPE)
		, (SELECT object_type, change_type, dpnd_object_name
			FROM ssf.dpnd_dependency_object WITH (NOLOCK)
			WHERE header_object_id = r.header_object_id
			AND dependency_type = 'OUTPUT'
			FOR XML RAW ('output_dependency'), ELEMENTS, TYPE)
		, (SELECT h.object_type, h.dpnd_object_name
			FROM ssf.dpnd_header_object h WITH (NOLOCK)
			JOIN ssf.dpnd_dependency_object d WITH (NOLOCK)
			ON h.header_object_id = d.header_object_id
			WHERE CASE WHEN ISNULL(PARSENAME(d.dpnd_object_name, 3),PARSENAME(d.dpnd_object_name, 2)) IS NULL
				THEN OBJECT_SCHEMA_NAME(OBJECT_ID(d.dpnd_object_name)) + '.' + d.dpnd_object_name
				ELSE d.dpnd_object_name
				END = r.dpnd_object_name
			FOR XML RAW ('referencing_dependency'), ELEMENTS, TYPE)
		, (SELECT comment_sequence, code_comment
			FROM ssf.dpnd_object_comment WITH (NOLOCK)
			WHERE header_object_id = r.header_object_id
			ORDER BY comment_sequence
			FOR XML RAW ('object_comment'), ELEMENTS, TYPE)
	FROM ssf.dpnd_header_object
	WHERE header_object_id = r.header_object_id
	FOR XML RAW ('object_header'), ELEMENTS, TYPE
	) AS XML (ssf.object_header)) AS object_header
	FROM ssf.dpnd_header_object r WITH (NOLOCK)
	WHERE r.dpnd_object_name = ISNULL(NULLIF(@dpnd_object_name, ''), r.dpnd_object_name)
)
GO
